﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using MySql.Data.MySqlClient;

namespace ATM.DBStublayer
{
   public class AccountDB
    {
        public static DataGridView data = new DataGridView();
        public int count = 0;
        public string face;
        public float num, num2, result, senderBalance, reciverBalance;//3
        public int chekId = 0, chekBalance = 0 ,chekAccountNu = 0, balanceID;

       // get the transaction date and time
        DateTime transactionDate = DateTime.Now;

        public void balance(string acco)
        {

            chekBalance = 50000;
        }

        public void balanceByID(string IDD)
        {
            try
            {
                string myconnection = "datasource=localhost;port=3306;username=root;password=1234";
                MySqlConnection myconn = new MySqlConnection(myconnection);
                MySqlCommand SelectCommand = new MySqlCommand("SELECT * FROM atm.atm_customers where ID='" + IDD + "';", myconn);
                MySqlDataReader myreader;

                myconn.Open();
                myreader = SelectCommand.ExecuteReader();
                while (myreader.Read())
                {
                    balanceID = int.Parse((myreader["balance"].ToString()));
                }

                myconn.Close();
                // return chekBalance;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }


        public void ID(string acco)
        {
            try
            {
                string myconnection = "datasource=localhost;port=3306;username=root;password=1234";
                MySqlConnection myconn = new MySqlConnection(myconnection);
                MySqlCommand SelectCommand = new MySqlCommand("SELECT * FROM atm.atm_customers where Account_Number='" + acco + "';", myconn);
                MySqlDataReader myreader;

                myconn.Open();
                myreader = SelectCommand.ExecuteReader();
                while (myreader.Read())
                {
                    chekId = int.Parse((myreader["ID"].ToString()));
                }

                myconn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        public void AcountNumber(string id)
        {
            try
            {
                string myconnection = "datasource=localhost;port=3306;username=root;password=1234";
                MySqlConnection myconn = new MySqlConnection(myconnection);
                MySqlCommand SelectCommand = new MySqlCommand("SELECT * FROM atm.atm_customers where ID='" + id + "';", myconn);
                MySqlDataReader myreader;

                myconn.Open();
                myreader = SelectCommand.ExecuteReader();
                while (myreader.Read())
                {
                    chekAccountNu = int.Parse((myreader["Account_Number"].ToString()));
                }

                myconn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        public bool balnceChange(string Acc, int balan)
        {
            return true;
        }


        public void logoing(string data)
        {

           
            count = 1;

        }



        public void interfaccee(string inte)
        {
            string myconnection = "datasource=localhost;port=3306;username=root;password=1234";
            MySqlConnection myconn = new MySqlConnection(myconnection);

            MySqlCommand SelectCommand = new MySqlCommand("SELECT * FROM atm.atm_customers where Account_Number='" + inte + "';", myconn);// hagep kol elly fe el data base elly el accnum bta3o kaza elly hwa ma7tot fe x

            try
            {
                MySqlDataAdapter sda = new MySqlDataAdapter();// de elly bt3mel update lel table
                sda.SelectCommand = SelectCommand;// de elly bt2olak ha3mel update feen wbema en ana 3amel select * fhay3mel update fe ay  7eta fe elgadwal ana 2ayelo 3leha e3mel feh ta3'yeer
                DataTable dbdataset = new DataTable();// ba5od data mn el database server wba3melha store fe memory mo2aqata
                sda.Fill(dbdataset);// ba7ot el update elly ana 3amalto fe elgadwal elly ma7tot mo2aqatan fe el memory
                BindingSource bsource = new BindingSource();// hna barbot el memory elmo2aqta bel database el2aslya 
                bsource.DataSource = dbdataset;//de el function elly btorbot
                data.DataSource = bsource; // bya5od el update mn el memory elmo2aqat wby7otaha fe mkanha fe eldatabase
                sda.Update(dbdataset);//by3melha isnsert fe eldatabase wkda 5alas elupdate 5alas w5ado mn el memory elmo2aqata w7ataha fe el database belupdate
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

        }

        public float GetBalance(int accountNumber) {

            try
            {
                string myconnection = "datasource=localhost;port=3306;username=root;password=1234";
                MySqlConnection myconn = new MySqlConnection(myconnection);
                MySqlCommand SelectCommand = new MySqlCommand("SELECT * FROM atm.atm_customers where Account_Number='" + accountNumber + "';", myconn);
                
                MySqlDataReader myreader;

                myconn.Open();
                myreader = SelectCommand.ExecuteReader();
                 
                while (myreader.Read())

                {
                     
                    num = myreader.GetFloat("balance");// retrieve the balance from the DB .
                }

                myconn.Close();
               
                    
              
            }
            catch 
            {
                throw;
            }

           return num; 
        }



        public void RecordDeposit(string accountNumber,float balance  , int amount) {

            // recording the transaction in the Database 
              string myconnection3 = "datasource=localhost;port=3306;username=root;password=1234";
              MySqlConnection myconn3 = new MySqlConnection(myconnection3);

              MySqlCommand InsertCommand = new MySqlCommand("INSERT INTO atm.History VALUES ('"+accountNumber+"' , 2 , 'DEPOSIT' , '"+amount+"', '"+balance+"' , '"+transactionDate+"' );", myconn3);
              MySqlDataReader myreader3;

              myconn3.Open();
              myreader3 = InsertCommand.ExecuteReader();
              myconn3.Close();    

        
        
        }

        public void RecordWithdraw(string accountNumber , float balance, int amount)
        {

            // recording the transaction in the Database 
            string myconnection3 = "datasource=localhost;port=3306;username=root;password=1234";
            MySqlConnection myconn3 = new MySqlConnection(myconnection3);

            MySqlCommand InsertCommand = new MySqlCommand("INSERT INTO atm.History VALUES ('" + accountNumber + "' , 1 , 'WITHDRAW' , '" + amount + "', '" + balance + "' , '" + transactionDate + "' );", myconn3);
            MySqlDataReader myreader3;

            myconn3.Open();
            myreader3 = InsertCommand.ExecuteReader();
            myconn3.Close();



        }


        public void RecordTransfer(string accountNumber, float balance, int amount)
        {

            // recording the transaction in the Database 
            string myconnection3 = "datasource=localhost;port=3306;username=root;password=1234";
            MySqlConnection myconn3 = new MySqlConnection(myconnection3);

            MySqlCommand InsertCommand = new MySqlCommand("INSERT INTO atm.History VALUES ('" + accountNumber + "' , 3 , 'TRANSFER' , '" + amount + "', '" + balance + "' , '" + transactionDate + "' );", myconn3);
            MySqlDataReader myreader3;

            myconn3.Open();
            myreader3 = InsertCommand.ExecuteReader();
            myconn3.Close();



        }
       
    }
}
